DROP DATABASE IF EXISTS `VIRTUALDB`
GO
CREATE DATABASE IF NOT EXISTS `VIRTUALDB`
GO
USE VIRTUALDB
GO
CREATE TABLE IF NOT EXISTS users ( 
    userid          	int(11) AUTO_INCREMENT NOT NULL,
    useraka         	varchar(20) NULL,
    useremail       	varchar(40) NULL,
    lastUpdateDate  	datetime NULL,
    userName        	varchar(50) NOT NULL,
    reduzedId       	varchar(6) NULL,
    registrationDate	datetime NULL,
    registrationId  	varchar(36) NOT NULL,
    PRIMARY KEY(userId)
)
GO
CREATE TABLE IF NOT EXISTS userlogins ( 
    loginid    	int(11) AUTO_INCREMENT NOT NULL,
    accessCount	int(11) NULL,
    createdate 	datetime NULL,
    userlogin  	varchar(255) NULL,
    passwd     	varchar(255) NULL,
    validate   	datetime NULL,
    userid     	int(11) NOT NULL,
    PRIMARY KEY(loginid)
)
GO
CREATE TABLE IF NOT EXISTS roles ( 
    rolename   	varchar(60) NOT NULL,
    allowing   	bit(1) NOT NULL,
    description	varchar(255) NULL,
    worker     	bit(1) NOT NULL,
    PRIMARY KEY(rolename)
)
GO
CREATE TABLE IF NOT EXISTS loginroles ( 
    loginid 	int(11) NOT NULL,
    rolename	varchar(60) NOT NULL 
    )
GO
CREATE TABLE IF NOT EXISTS facesmember ( 
    membertype        	char(1) NOT NULL,
    id                	int(11) AUTO_INCREMENT NOT NULL,
    description       	varchar(255) NULL,
    name              	varchar(50) NULL,
    facesManagedBeanId	int(11) NULL,
    PRIMARY KEY(id)
)
GO
CREATE TABLE IF NOT EXISTS menu ( 
    menutype     	char(1) NOT NULL,
    idmenu       	int(11) AUTO_INCREMENT NOT NULL,
    description  	varchar(255) NULL,
    disabled     	bit(1) NULL,
    label        	varchar(255) NOT NULL,
    menuHelpLink 	varchar(255) NULL,
    menuTopHint  	varchar(255) NULL,
    name         	varchar(255) NOT NULL,
    noLogged     	bit(1) NULL,
    menuorder    	int(11) NOT NULL,
    expanded     	bit(1) NULL,
    idgroup      	int(11) NULL,
    facesActionId	int(11) NULL,
    PRIMARY KEY(idmenu)
)
GO
CREATE TABLE IF NOT EXISTS menuroles ( 
    idmenu  	int(11) NOT NULL,
    rolename	varchar(60) NOT NULL 
    )
GO
CREATE TABLE clients ( 
    federalIdNumber     	varchar(255) NULL,
    federalIdType       	varchar(255) NULL,
    stateIdEmiter       	varchar(255) NULL,
    stateIdNumber       	varchar(255) NULL,
    stateIdType         	varchar(255) NULL,
    clientid            	int(11) NOT NULL,
    authorizedretailerid	int(11) NOT NULL,
    PRIMARY KEY(clientid)
)
GO
CREATE TABLE friendlytext ( 
    locale	varchar(255) NOT NULL,
    text  	varchar(255) NOT NULL,
    PRIMARY KEY(locale,text)
)
GO
CREATE TABLE logcontrol ( 
    logtype	int(11) NOT NULL,
    id     	int(11) AUTO_INCREMENT NOT NULL,
    message	varchar(255) NOT NULL,
    tag    	varchar(255) NOT NULL,
    timeLog	datetime NOT NULL,
    loginid	int(11) NOT NULL,
    PRIMARY KEY(id)
)
GO
CREATE TABLE rolefriendlyname ( 
    roleName           	varchar(60) NOT NULL,
    friendlyName_locale	varchar(255) NOT NULL,
    friendlyName_text  	varchar(255) NOT NULL,
    PRIMARY KEY(roleName,friendlyName_locale,friendlyName_text)
)
GO
ALTER TABLE logcontrol
    ADD CONSTRAINT FK5CF84D991BABEBC7
	FOREIGN KEY(loginid)
	REFERENCES userlogins(loginid)
GO
CREATE TABLE retailers ( 
    retailerid	int(11) NOT NULL,
    PRIMARY KEY(retailerid)
)
GO
CREATE TABLE workers ( 
    id      	int(11) AUTO_INCREMENT NOT NULL,
    clientid	int(11) NULL,
    rolename	varchar(255) NULL,
    userid  	int(11) NULL,
    PRIMARY KEY(id)
)
GO
-- end tables

-- Start Indexs
CREATE INDEX FK_Menu_FacesActionID
    ON menu(facesActionId)
GO
CREATE UNIQUE INDEX Unique_Menu_Name
    ON menu(name)
GO
CREATE UNIQUE INDEX Unique_Menu_Menutype
    ON menu(menutype, name)
GO
CREATE UNIQUE INDEX Unique_MenuHelpLink
    ON menu(menuHelpLink)
GO
CREATE INDEX FK_MenuRoles_IdMenu
    ON menuroles(idmenu)
GO
CREATE INDEX FK_MenuRoles_RoleName
    ON menuroles(rolename)
GO
CREATE UNIQUE INDEX membertype
    ON facesmember(membertype, name)
GO
CREATE INDEX FKB86FDFD09498B6E5
    ON facesmember(facesManagedBeanId)
GO
CREATE UNIQUE INDEX userlogin
    ON userlogins(userlogin)
GO
CREATE INDEX FK8943A5B5FFF7BC97
    ON userlogins(userid)
GO
CREATE INDEX FK98DED8F41BABEBC7
    ON loginroles(loginid)
GO
CREATE INDEX FK98DED8F421F2EF1D
    ON loginroles(rolename)
GO
CREATE UNIQUE INDEX rolename
    ON loginroles(rolename)
GO
CREATE INDEX FK334B860820492926
    ON clients(authorizedretailerid)
GO
CREATE INDEX FK334B860867F1C497
    ON clients(clientid)
GO
CREATE INDEX FKF1AE5D6C6B3D0781
    ON rolefriendlyname(friendlyName_locale, friendlyName_text)
GO
CREATE INDEX FKF1AE5D6C21F2EF1D
    ON rolefriendlyname(roleName)
GO
CREATE UNIQUE INDEX friendlyName_locale
    ON rolefriendlyname(friendlyName_locale, friendlyName_text)
GO
CREATE INDEX FK5AE81CB557E9841
    ON workers(clientid)
GO
CREATE INDEX FK5AE81CB521F2EF1D
    ON workers(rolename)
GO
CREATE INDEX FK5AE81CB5FFF7BC97
    ON workers(userid)
GO
CREATE UNIQUE INDEX useremail
    ON users(useremail)
GO
CREATE UNIQUE INDEX registrationid
    ON users(registrationid)
GO
CREATE INDEX FK5CF84D991BABEBC7
    ON logcontrol(loginid)
GO
CREATE INDEX FKA6235583EDA0B826
    ON retailers(retailerid)
GO
-- end indexs

-- start constraints
ALTER TABLE menu
    ADD CONSTRAINT UniqueName
	UNIQUE (name)
GO
ALTER TABLE menu
    ADD CONSTRAINT UniqueMenuType
	UNIQUE (menutype, name)
GO
ALTER TABLE menu
    ADD CONSTRAINT UniqueMenuHelpLink
	UNIQUE (menuHelpLink)
GO
ALTER TABLE menu
    ADD CONSTRAINT FK33155F52CA2EB5
	FOREIGN KEY(idgroup)
	REFERENCES menu(idmenu)
GO
ALTER TABLE menuroles
    ADD CONSTRAINT FKA608FCBE4CFE4799
	FOREIGN KEY(idmenu)
	REFERENCES menu(idmenu)
GO
ALTER TABLE users
    ADD CONSTRAINT UniqueUserEmail
	UNIQUE (useremail)
GO
ALTER TABLE users
    ADD CONSTRAINT UniqueRegistrationId
	UNIQUE (registrationid)
GO
ALTER TABLE userlogins
    ADD CONSTRAINT UniqueUserlogin
	UNIQUE (userlogin)
GO
ALTER TABLE userlogins
    ADD CONSTRAINT FK8943A5B5FFF7BC97
	FOREIGN KEY(userid)
	REFERENCES users(userid)
GO
ALTER TABLE loginroles
    ADD CONSTRAINT UniqueRolename
	UNIQUE (rolename)
GO
ALTER TABLE loginroles
    ADD CONSTRAINT FK98DED8F421F2EF1D
	FOREIGN KEY(rolename)
	REFERENCES roles(rolename)
GO
ALTER TABLE loginroles
    ADD CONSTRAINT FK98DED8F41BABEBC7
	FOREIGN KEY(loginid)
	REFERENCES userlogins(loginid)
GO
ALTER TABLE facesmember
    ADD CONSTRAINT UniqueMembertype
	UNIQUE (membertype, name)
GO
ALTER TABLE facesmember
    ADD CONSTRAINT FKB86FDFD09498B6E5
	FOREIGN KEY(facesManagedBeanId)
	REFERENCES facesmember(id)
GO
ALTER TABLE clients
    ADD CONSTRAINT FK334B860867F1C497
	FOREIGN KEY(clientid)
	REFERENCES users(userid)
GO
ALTER TABLE clients
    ADD CONSTRAINT FK334B860820492926
	FOREIGN KEY(authorizedretailerid)
	REFERENCES retailers(retailerid)
GO
ALTER TABLE rolefriendlyname
    ADD CONSTRAINT UniqueFriendlyName_locale
	UNIQUE (friendlyName_locale, friendlyName_text)
GO
ALTER TABLE rolefriendlyname
    ADD CONSTRAINT FKF1AE5D6C6B3D0781
	FOREIGN KEY(friendlyName_locale, friendlyName_text)
	REFERENCES friendlytext(locale, text)
GO
ALTER TABLE rolefriendlyname
    ADD CONSTRAINT FKF1AE5D6C21F2EF1D
	FOREIGN KEY(roleName)
	REFERENCES roles(rolename)
GO
ALTER TABLE workers
    ADD CONSTRAINT FK5AE81CB5FFF7BC97
	FOREIGN KEY(userid)
	REFERENCES users(userid)
GO
ALTER TABLE workers
    ADD CONSTRAINT FK5AE81CB557E9841
	FOREIGN KEY(clientid)
	REFERENCES clients(clientid)
GO
ALTER TABLE workers
    ADD CONSTRAINT FK5AE81CB521F2EF1D
	FOREIGN KEY(rolename)
	REFERENCES roles(rolename)
GO
ALTER TABLE retailers
    ADD CONSTRAINT FK_Clients_Retailers_RetailerId
	FOREIGN KEY(retailerid)
	REFERENCES clients(clientid)
GO